package com.systop.util;


import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Date;
import java.util.Properties;

public class JdbcUtil {

    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static {
        InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        driver = properties.getProperty("jdbc.driver");
        url = properties.getProperty("jdbc.url");
        username = properties.getProperty("jdbc.username");
        password = properties.getProperty("jdbc.password");
    }

    public static Connection getConnection() {
        Connection con = null;
        // 加载驱动的类
        try {
            Class.forName(driver);
            /* 运行驱动类 */
            con = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return con;
    }

    // 链接,预编译的代码,结果集
    public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void textadd() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 获取链接
        try {
            conn = getConnection();
            String sql = "INSERT INTO t_notice(title,content,pushtime,createtime)VALUES(?,?,?,?)";
            // 运行sql 代码
            pstmt = conn.prepareStatement(sql);
            // 传递参数
            pstmt.setString(1, "标题");
            pstmt.setString(2, "内容");
            pstmt.setDate(3,new java.sql.Date(new Date().getTime()));
            pstmt.setDate(4,new java.sql.Date(new Date().getTime()));
            // 执行代码,获取结果集
            pstmt.executeUpdate();


        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pstmt, rs);
        }
    }

    @Test
    public void test() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 获取链接
        try {
            conn = getConnection();
			String sql ="select * from notice";
			// 运行sql 代码
			pstmt = conn.prepareStatement(sql);
			// 传递参数
//			pstmt.setString(1, "2");
			// 执行代码,获取结果集
			rs = pstmt.executeQuery();
			// 通过数据库的名字来获取代码
			while (rs.next()) {
				String username = rs.getString("name");
				System.out.println(username);
			}

//            String sql = "insert into teacher(id,name)values(?,?)";
//            // 运行sql 代码
//            pstmt = conn.prepareStatement(sql);
//            // 传递参数
//            pstmt.setString(1, "5");
//            pstmt.setString(2, "张5");
//            // 执行代码,获取结果集
//            pstmt.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pstmt, rs);
        }

    }
}
